﻿using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess.UserCode
{
    public partial class BsHalfTransferDataAccess
    {
        public static DataTable SelectDialogScheduleDetailList(string BillDate, string strWorkOrderNo, string strScheduleID, string strProductID, string strProductName, int pageIndex, int pageSize, out int m_count)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"select sd.*,sp.ScheduleProcessID,sp.OrderNum,sp.ProcessID,
		                                    sp.OurSourceBackNum,sp.OutSourceUnqualifyNum,
		                                    --ISNULL(sp.OurSourceBackNum,0)-ISNULL(sp.OutSourceUnqualifyNum,0) as OutSourceQualifyNum,
		                                    sp.CheckUnqualifyNum,sp.IsCheck,sp.IsOutSource,pc.ProcessName,
		                                    p.ProductID,p.ProductName,p.Unit,p.Specifications
                                    from BS_ScheduleDetail sd
                                    left join BS_ScheduleProcess sp on sp.ScheduleDetailID=sd.ScheduleDetailID
                                    left join BS_OrderDetail od on od.WorkOrderNo=sd.WorkOrderNo
                                    left join MD_Product p on p.ProductID=od.ProductID
                                    left join MD_Process pc on pc.ProcessID=sp.ProcessID
                                    left join BS_Schedule s on s.ScheduleID=sd.ScheduleID
                                    where sd.IsFinish<>'1' or sd.IsFinish is null and sp.ScheduleProcessID not in (select ScheduleProcessID 
				                                    from BS_HalfTransfer
				                                    group by ScheduleProcessID,ScheduleDetailID) 
                                        and CONVERT(varchar(10),@cBillDate,23) between CONVERT(varchar(10),s.FromDate,23) and CONVERT(varchar(10),s.EndDate,23)";

                    dpc.AddWithValue("@cBillDate", BillDate);
                    if (!string.IsNullOrEmpty(strWorkOrderNo))
                    {
                        strcmd += " and sd.WorkOrderNo like @strWorkOrderNo";
                        dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                    }
                    if (!string.IsNullOrEmpty(strScheduleID))
                    {
                        strcmd += " and sd.ScheduleID like @strScheduleID";
                        dpc.AddWithValue("@strScheduleID", "%" + strScheduleID + "%");
                    }
                    if (!string.IsNullOrEmpty(strProductID))
                    {
                        strcmd += " and p.ProductID like @strProductID";
                        dpc.AddWithValue("@strProductID", "%" + strProductID + "%");
                    }
                    if (!string.IsNullOrEmpty(strProductName))
                    {
                        strcmd += " and p.ProductName like @strProductName";
                        dpc.AddWithValue("@strProductName", "%" + strProductName + "%");
                    }
                    strcmd += " order by sd.ScheduleDetailID asc,sp.OrderNum asc";
                    return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectList(string HalfTransferID, string strFrom, string strTo, int IHalfTransferCount, out int icurPageIndex, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                #region SQL Server
                string strcmd = @"select A.* from V_HalfTransfer A WHERE 1=1 ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                if (!string.IsNullOrEmpty(HalfTransferID))
                {
                    strcmd += " and A.HalfTransferID LIKE @HalfTransferID";
                    dpc.AddWithValue("@HalfTransferID", "%" + HalfTransferID + "%");
                }

                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and A.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strTo))
                {
                    strcmd += " and A.BillDate <= @strTo";
                    dpc.AddWithValue("@strTo", strTo);
                }


                strcmd += " ORDER BY A.HalfTransferID DESC";

                return DataAccessCommon.ExecuteDataTablePagingExt(strcmd, out icurPageIndex, pageIndex, PageSize, dpc, IHalfTransferCount, out m_count);
                #endregion
            }
            catch
            {
                throw;
            }
        }
    }
}
